package db.strategy;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.sql.Connection;
import java.text.SimpleDateFormat;

import db.Database;
import db.commodity.CommodityDB;
import db.commodity.CommodityTypeDB;
import dbservice.strategy.StrategyDBService;
import po.AccountBookPo;
import po.CommodityPo;
import po.CommodityTypePo;
import po.GiftPo;  
import po.Po;
import po.CouponsPo;
import po.DiscountPackagePo;
import po.DiscountPo;
import po.商品列表单项po;
public class StrategyDB implements StrategyDBService {
	AccountBookPo ab;
	public StrategyDB(AccountBookPo ab) {
		this.ab=ab;
	}
	
	public Po add(GiftPo gift){
		Po m=new Po();
		String date_from=new SimpleDateFormat("yyyy-MM-dd").format(gift.getFrom());
		String date_to=new SimpleDateFormat("yyyy-MM-dd").format(gift.getTo());
        int level_client=gift.get客户级别();
        long total_require=gift.get总额条件();
		String commodityID=gift.get商品编号();
		String commodityName=gift.get商品名称();
		String commodityModel=gift.get商品型号();
		int num=gift.getNum();
		String createT_gift="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_strategy_gift` ( `f_id` int(8) NOT NULL AUTO_INCREMENT, `f_from` date NOT NULL, `f_to` date NOT NULL,`f_client` int(8) NOT NULL,`f_totalRequire` BIGINT(0) NOT NULL, `f_commodityID` varchar(20) NOT NULL, `f_commodityName` varchar(20) NOT NULL, `f_commodityModel` varchar(20) NOT NULL, `f_giftNum` int(8) NOT NULL, PRIMARY KEY (`f_id`), UNIQUE (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		try {
			Statement s = c.createStatement();
			s.execute(createT_gift);
			ResultSet rs=s.executeQuery("select f_stockNum from "+ab.getName()+"_commodity where f_commodityID='"+commodityID+"'");
		    int stockNum=0;
			while(rs.next()){
		    	 stockNum=rs.getInt(1);
		    }
		   if(num<=stockNum){
			   s.execute("insert into "+ab.getName()+"_strategy_gift(f_from,f_to,f_client,f_totalRequire,f_commodityID,f_commodityName,f_commodityModel,f_giftNum) values('"+date_from+"','"+date_to+"','"+level_client+"','"+total_require+"','"+commodityID+"','"+commodityName+"','"+commodityModel+"','"+num+"')");
			   m.successed();
		
		   }else{
			   m.failed(commodityName+"库存数量为"+stockNum+",您填写的数量大于库存数量！");
		   }
		   s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		
		return m;
    }
    public Po add(DiscountPo discount){
    	Po m=new Po();
		String date_from=new SimpleDateFormat("yyyy-MM-dd").format(discount.getFrom());
		String date_to=new SimpleDateFormat("yyyy-MM-dd").format(discount.getTo());
        int level_client=discount.get客户级别();
        long total_require=discount.get总额条件();
		long dis=discount.getDiscount();
		String createT_discount="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_strategy_discount` ( `f_id` int(8) NOT NULL AUTO_INCREMENT, `f_from` date NOT NULL, `f_to` date NOT NULL,`f_client_level` int(8) NOT NULL,`f_totalRequire` BIGINT(0) NOT NULL, `f_discount` BIGINT(0) NOT NULL, PRIMARY KEY (`f_id`), UNIQUE (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		try {
			Statement s = c.createStatement();
			s.execute(createT_discount);
			   s.execute("insert into "+ab.getName()+"_strategy_discount(f_from,f_to,f_client_level,f_totalRequire,f_discount) values('"+date_from+"','"+date_to+"','"+level_client+"','"+total_require+"','"+dis+"')");
			   m.successed();
		
		   s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
    }

    
    public Po add(DiscountPackagePo discountpackage){
    	Po m=new Po();int key=0;
    	String date_from=new SimpleDateFormat("yyyy-MM-dd").format(discountpackage.getFrom());
		String date_to=new SimpleDateFormat("yyyy-MM-dd").format(discountpackage.getTo());
		String name=discountpackage.getPackage_name();
		int num=discountpackage.getNum();
		long price=discountpackage.getPrice();
		List<商品列表单项po> list_cm=discountpackage.getCommoditypackage();
		
		
		String createT_discountpackage="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_strategy_discountpackage` ( `f_from` date NOT NULL, `f_to` date NOT NULL,`f_name` varchar(40) NOT NULL,`f_num` int(8) NOT NULL,`f_price` BIGINT(0) NOT NULL, `f_item` int(8) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`f_item`), UNIQUE (`f_item`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		String createT_discountpackage_item="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_strategy_discountpackage_item`(`f_id` int(8) NOT NULL AUTO_INCREMENT,`f_key` int(8) NOT NULL,`f_commodityName` varchar(40) NOT NULL,`f_commodityID` varchar(40) NOT NULL,`f_commodityModel` varchar(40) NOT NULL,`f_num` int(8) NOT NULL,PRIMARY KEY (`f_id`), UNIQUE (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		try {
			Statement s = c.createStatement();
			s.execute(createT_discountpackage);
			s.execute(createT_discountpackage_item);
			//检查name
			ResultSet r=s.executeQuery("select * from "+ab.getName()+"_strategy_discountpackage where f_name='"+name+"'");
			while(r.next()){
				m.failed("您填写的特价包名已存在！");
				return m;
			}
			
			s.execute("insert into "+ab.getName()+"_strategy_discountpackage(f_from,f_to,f_name,f_num,f_price) values('"+date_from+"','"+date_to+"','"+name+"','"+num+"','"+price+"')");
			
			ResultSet rs=s.executeQuery("select f_item from "+ab.getName()+"_strategy_discountpackage where f_name='"+name+"'");
			while(rs.next()){
				key=rs.getInt(1);
			}
			
			for(int i=0;i<list_cm.size();i++){
				ResultSet rs1=s.executeQuery("select f_stockNum from "+ab.getName()+"_commodity where f_commodityID='"+list_cm.get(i).get商品编号()+"'");
				int stocktmp = 0;
				while(rs1.next()){
					 stocktmp=rs1.getInt(1);
				}
				if(stocktmp<num*list_cm.get(i).get数量()){
					m.failed("您填写的"+list_cm.get(i).get商品名称()+"数量已经超过库存数量！");
					s.execute("delete from "+ab.getName()+"_strategy_discountpackage where f_name='"+name+"'");
					s.close();
					return m;
				}else{
					s.addBatch("insert into "+ab.getName()+"_strategy_discountpackage_item(f_key,f_commodityName,f_commodityID,f_commodityModel,f_num) values('"+key+"','"+list_cm.get(i).get商品名称()+"','"+list_cm.get(i).get商品编号()+"','"+list_cm.get(i).get商品型号()+"','"+list_cm.get(i).get数量()+"')");
					//减少相应的cm库存数量
	                s.addBatch("update "+ab.getName()+"_commodity set f_stockNum=(f_stockNum-"+num*list_cm.get(i).get数量()+") where f_commodityID='"+list_cm.get(i).get商品编号()+"'");
	                s.addBatch("update "+ab.getName()+"_commodity set f_totalMoney=(f_totalMoney-f_stockAveragePrice*"+num*list_cm.get(i).get数量()+") where f_commodityID='"+list_cm.get(i).get商品编号()+"'");
	                s.addBatch("update "+ab.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+list_cm.get(i).get商品编号()+"'");
				}
				
			}
			s.executeBatch();
			
			
			//添加特价包到商品中
			CommodityTypeDB cm_type=new CommodityTypeDB(ab);
			CommodityTypePo commoditytype=new CommodityTypePo();
			commoditytype.set名称("特价包");
			cm_type.add(commoditytype);
			CommodityPo package_cm=new CommodityPo();
			package_cm.set名称(name);
			package_cm.set型号("特价包_"+name);
			package_cm.set所属分类("特价包");
			package_cm.set库存数量(num);
			package_cm.set库存数量警戒值(0);
			package_cm.set售价(price);
            long total=0;
            for(int i=0;i<list_cm.size();i++){
                  ResultSet rs2=s.executeQuery("select f_stockAveragePrice from "+ab.getName()+"_commodity where f_commodityID='"+list_cm.get(i).get商品编号()+"'");
                  while(rs2.next()){
                	  list_cm.get(i).set单价(rs2.getLong(1));
                  }
                  total+=list_cm.get(i).get单价()*list_cm.get(i).get数量();
            }
            package_cm.set库存均价(total);
            package_cm.set库存总价(total*num);
            package_cm.set进价(total);
            CommodityDB cmdb=new CommodityDB(ab);
            cmdb.add(package_cm);
            m.successed();
            s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
    	return m;
    }
    
    
    public Po add(CouponsPo coupons){
    	Po m=new Po();
		String date_from=new SimpleDateFormat("yyyy-MM-dd").format(coupons.getFrom());
		String date_to=new SimpleDateFormat("yyyy-MM-dd").format(coupons.getTo());
        int level_client=coupons.get客户级别();
        long total_require=coupons.get总额条件();
		int num=coupons.getNum();           //数量
	    long value=coupons.getValue();      //面值
        
        
		String createT_discount="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_strategy_coupons` ( `f_id` int(8) NOT NULL AUTO_INCREMENT, `f_from` date NOT NULL, `f_to` date NOT NULL,`f_client_level` int(8) NOT NULL,`f_totalRequire` BIGINT(0) NOT NULL,`f_num` int(8) NOT NULL, `f_value` BIGINT(0) NOT NULL, PRIMARY KEY (`f_id`), UNIQUE (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		try {
			Statement s = c.createStatement();
			s.execute(createT_discount);
			   s.execute("insert into "+ab.getName()+"_strategy_coupons(f_from,f_to,f_client_level,f_totalRequire,f_num,f_value) values('"+date_from+"','"+date_to+"','"+level_client+"','"+total_require+"','"+num+"','"+value+"')");
			   m.successed();
		
		   s.close();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;   
		}
    
    
    
    
    
    
}
